﻿using Aspose.Cells;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace PMBF.Core.Plugins
{
    public class ExcelHelper
    {
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dtData">数据List，</param>
        /// <param name="SheetName">SheetName，名称少于40个字符，如果为空默认Sheet1</param>
        /// <param name="Exportpro">Excel列字段</param>
        /// <param name="ExportTitles">Excel标题字段</param>
        public static byte[] DataToExcel<T>(List<T> dtData, String SheetName, List<string> Exportpro, List<string> ExportTitles, bool IsDataString = true) where T : new()
        {
            string sheetName = SheetName;
            if (sheetName.Trim() == string.Empty)
            {
                sheetName = "Sheet1";
            }
            if (Exportpro == null)
            {
                Exportpro = new List<string>();
            }
            if (sheetName.Length > 40)
            {
                sheetName = sheetName.Substring(0, 40);
            }

            Workbook workbook = new Workbook();
            workbook.Worksheets.Clear();
            workbook.Worksheets.Add(sheetName);
            workbook.Worksheets[0].AutoFitColumns();

            #region 标题样式

            //为单元格添加样式    
            Style style = workbook.CreateStyle();
            //设置居中
            style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
            //设置背景颜色
            style.ForegroundColor = System.Drawing.Color.FromArgb(192, 192, 192);
            style.Pattern = BackgroundType.Solid;
            style.Font.IsBold = true;
            style.Font.Name = "黑体";
            style.Borders[BorderType.BottomBorder].LineStyle = (CellBorderType.Thin);
            style.Borders[BorderType.TopBorder].LineStyle = (CellBorderType.Thin);
            style.Borders[BorderType.RightBorder].LineStyle = (CellBorderType.Thin);
            style.Borders[BorderType.LeftBorder].LineStyle = (CellBorderType.Thin);
            style.Borders.SetColor(System.Drawing.Color.Black);

            #endregion

            #region 内容样式

            //为单元格添加样式    
            Style style1 = workbook.CreateStyle();
            //设置居中
            style1.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
            style1.Pattern = BackgroundType.Solid;
            style1.Font.Name = "宋体";
            style1.Borders[BorderType.BottomBorder].LineStyle = (CellBorderType.Thin);
            style1.Borders[BorderType.TopBorder].LineStyle = (CellBorderType.Thin);
            style1.Borders[BorderType.RightBorder].LineStyle = (CellBorderType.Thin);
            style1.Borders[BorderType.LeftBorder].LineStyle = (CellBorderType.Thin);
            style1.Borders.SetColor(System.Drawing.Color.Black);

            #endregion

            T t0 = new T();
            int i0 = 0;
            int sheetindex = 0;
            int rowid = 1;

            foreach (string n in ExportTitles)
            {
                foreach (Aspose.Cells.Worksheet sheet in workbook.Worksheets)
                {
                    sheet.Cells[0, i0].PutValue(n);
                    sheet.Cells[0, i0].SetStyle(style);
                    sheet.Cells.SetRowHeight(0, 30);
                    sheet.Cells.SetColumnWidth(i0, 30);
                }
                i0++;
            }
            foreach (T t in dtData)
            {
                i0 = 0;
                foreach (string n in Exportpro)
                {
                    object obj = null;
                    if (t.GetType().FullName == "Newtonsoft.Json.Linq.JObject")
                    {
                        obj = Newtonsoft.Json.Linq.JObject.FromObject(t).Value<object>(n);
                    }
                    else
                    {
                        obj = t.GetType().GetProperties().SingleOrDefault(model => model.Name == n).GetValue(t, null);
                    }
                    if (obj == null)
                    {
                        obj = "";
                    }
                    if (IsDataString)
                    {
                        workbook.Worksheets[sheetindex].Cells[rowid, i0].PutValue(obj.ToString());
                    }
                    else
                    {
                        workbook.Worksheets[sheetindex].Cells[rowid, i0].PutValue(obj);
                    }
                    workbook.Worksheets[sheetindex].Cells[rowid, i0].SetStyle(style1);
                    workbook.Worksheets[sheetindex].Cells.SetRowHeight(rowid, 30);
                    i0++;
                }
                rowid = rowid + 1;
            }

            using (MemoryStream fileStream = new MemoryStream())
            {
                workbook.Save(fileStream, SaveFormat.Xlsx);
                fileStream.Position = 0;
                ExcelPackage packageexcel = new ExcelPackage(fileStream);
                packageexcel.Workbook.Worksheets.Delete("Evaluation Warning");
                return packageexcel.GetAsByteArray();
            }
        }

        /// <summary>
        /// DataTabel to Excel
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="columns"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static byte[] DataTableToExcel(DataTable dataTable, List<string> columns, string sheetName)
        {
            try
            {
                using (ExcelPackage pck = new ExcelPackage())
                {
                    ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);
                    ws.Cells["A1"].LoadFromDataTable(dataTable, true);
                    for (int i = 1; i <= columns.Count; i++) //设置excel列名
                    {
                        using (ExcelRange rng = ws.Cells[1, i])
                        {
                            rng.Style.Font.Bold = true;
                            rng.Value = columns[i - 1];
                        }
                    }
                    MemoryStream ms = new MemoryStream();
                    pck.SaveAs(ms);
                    ms.Flush();
                    ms.Position = 0;//指定当前流的位置从0开始

                    return ms.GetBuffer();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

    }
}
